# from collections import defaultdict
# from datetime import datetime, timedelta

from markupsafe import Markup
from odoo import fields, models, api, _, SUPERUSER_ID
from odoo.exceptions import UserError, ValidationError
from odoo import tools
import logging

_logger = logging.getLogger(__name__)


class PurchaseRequisitionLine(models.Model):
    _name = 'purchase.requisition.line'
    _description = 'Purchase Requisition Line'
    _order = 'id'

    requisition_id = fields.Many2one('purchase.requisition', 'Requisition', ondelete='cascade')
    requisition_state = fields.Selection(related='requisition_id.state', store=True)
    product_id = fields.Many2one('product.template', 'Material specification', ondelete='cascade', tracking=True,
                                 required=True)
    description_en = fields.Text(related="product_id.description_eng")
    name = fields.Char('Name')
    default_code = fields.Char('Default Code', related='product_id.default_code')
    description_chi = fields.Char('Material Desc')
    product_uom_id = fields.Many2one('uom.uom', 'Unit of purchase', related='product_id.uom_po_id', store=True)
    product_qty = fields.Float('Quantity', digits='Product Unit of Measure', default=1, help='Product Unit of Measure')
    qty_received = fields.Float('已处理数量', compute='_compute_qty_qty_received')
    qty_received_virtual = fields.Float('已处理数量')
    requirement_date = fields.Date('requirement date')
    domain_parent_ids = fields.Many2many('res.partner', string='supplier', compute='get_domain_partner')
    parent_id = fields.Many2one('res.partner', string='supplier')
    do_quantity = fields.Float('下单数量', default=0)
    unit_price = fields.Float('Unit Price', digits='Product Unit of Measure')
    state = fields.Selection([
        ('draft', 'Draft'),
        ('section_purchasing', 'Section Purchasing'),  # 部分采购
        ('purchasing', 'Purchasing'),  # 全部采购
        ('done', 'Done'),
        ('closed', 'Closed')
    ], default='draft', string='Status')

    @api.onchange('product_id')
    def onchange_product_id(self):
        if not self.product_id:
            return
        self.name = self.product_id.name

    def unlink(self):
        """
        删除时检查状态，允许草稿状态删除
        """
        if self.filtered(lambda x: x.state != 'draft'):
            raise ValidationError(_("删除出错，只允许草稿状态删除!"))
        ret = super(PurchaseRequisitionLine, self).unlink()
        return ret

    def done_back_section_purchasing(self):
        """
        未生成采购单不允许标记完成
        """
        for rec in self.filtered(lambda x: x.state == 'purchasing'):
            if not rec.requisition_id.purchase_ids:
                return {
                    'type': 'ir.actions.client',
                    'tag': 'display_notification',
                    'params': {
                        'title': 'Error',
                        'message': _(f'未生成采购单, 无法标记完成'),
                        'sticky': False,  # 设置为 False，表示通知不会一直显示
                        'timeout': 5000,  # 设置通知显示的时间（单位：毫秒），此处为 5 秒
                    }
                }
            rec.state = 'section_purchasing'

    def action_done(self):
        """
        未生成采购单不允许标记完成
        """
        for rec in self:
            if rec.product_qty != rec.qty_received:
                if self._context.get('skip_pur_close_reason', False):
                    rec.state = 'closed'
                else:
                    view_id = self.env.ref('sb_workflow_purchase.view_purchase_close_reason_wizard_form')
                    return {
                        'type': 'ir.actions.act_window',
                        'name': '输入关闭原因',
                        'res_model': 'purchase.close.reason.wizard',
                        'view_id': view_id.id,
                        'view_type': 'form',
                        'view_mode': 'form',
                        'target': 'new',
                        'context': {'default_pur_req_line_id': self.id, 'default_to_state': 'closed'},
                    }
            else:
                rec.state = 'purchasing'

    @api.onchange('product_qty')
    def onchange_product_qty(self):
        """
        初始值
        """
        for rec in self:
            rec.do_quantity = rec.product_qty - rec.qty_received

    # @api.onchange('parent_id')
    # def onchange_parent_id(self):
    #     self.unit_price = self.product_id.standard_price

    @api.onchange('do_quantity')
    def onchange_do_quantity(self):
        if self.do_quantity > self.product_qty - self.qty_received:
            raise ValidationError('总下单数量不能大于请购数量(包含已处理数量)')

    @api.depends('product_qty')
    def _compute_qty_qty_received(self):
        for record in self.sudo():
            # if record.state == 'closed':
            #     record.qty_received = 0
            #     continue
            if not isinstance(record.id, int):
                record.qty_received = 0
                continue
            if not record.product_id.product_variant_id:
                record.qty_received = 0
                continue
            query = f"""
            select distinct spre.pr_line_id AS prl_id,
                 spre.do_quantity
            FROM purchase_order_line pol
                 left join sb_pr_requisition_line_po_line spre on spre.po_line_id = pol.id
                 left join purchase_requisition_line prl on prl.id = spre.pr_line_id
            where spre.pr_line_id={record.id} and pol.state != 'cancel'
            """
            self.env.cr.execute(query)
            qty_received = self.env.cr.fetchall()
            record.qty_received = sum([i[1] for i in qty_received])
            # domain = [('pr_line_id', '=', record.id),
            #           ('po_state', '!=', 'cancel'),
            #           ('product_id', '=', record.product_id.id)]
            # purchase_line = self.env['sb.pr.requisition.line.po.line'].sudo().search(domain)
            # record.qty_received = sum(purchase_line.mapped('do_quantity'))
            # purchase = record.requisition_id.purchase_ids.filtered(lambda x: x.state != 'cancel')

    #         order_line = self.with_user(SUPERUSER_ID).env['purchase.order.line'].search([
    #             ('order_id', 'in', purchase.ids), ('product_id', '=', record.product_id.product_variant_id.id)])
    #         # 修复申购单有同产品多条明细时，PO->PR的关联错误问题
    #         if len(order_line.filtered(lambda x: x.do_qty > 0).mapped(lambda x: x.id)) > 1 \
    #                 and len(order_line.filtered(lambda x: x.do_qty > 0).mapped(lambda x: x.pr_line_id)) == 1 \
    #                 and record.id not in order_line.mapped(lambda x: x.pr_line_id.id):
    #             if order_line.filtered(lambda x: x.do_qty == record.product_qty):
    #                 repair_pr_ref_row = order_line.filtered(lambda x: x.do_qty == record.product_qty)[-1]
    #                 repair_pr_ref_row.pr_line_id = record
    #                 order_line = self.with_user(SUPERUSER_ID).env['purchase.order.line'].search([
    #                     ('order_id', 'in', purchase.ids), ('product_id', '=', record.product_id.product_variant_id.id)])
    #         qty_received = sum(order_line.filtered(lambda x: x.pr_line_id == record).mapped(lambda x: x.do_qty))
    #         product = order_line.filtered(lambda x: x.product_id.product_tmpl_id.id == record.product_id.id)
    #         if record.state in ('section_purchasing', 'purchasing') and not qty_received:
    #             product = order_line.filtered(
    #                 lambda x: x.product_id.product_tmpl_id.id == record.product_id.id \
    #                           and x.product_qty == record.product_qty
    #             )
    #         qty_received_old = sum(product.mapped('product_qty'))
    #         # 修复PO关联到PR一对一关联时接收数量大于申购数量问题
    #         error_pr_rel_row = order_line.filtered(
    #             lambda x: x.pr_line_id.id == record.id and x.do_qty > x.pr_line_id.product_qty)
    #         if len(error_pr_rel_row) == 1:
    #             error_pr_rel_row.do_qty = record.product_qty
    #         if 0 in product.mapped(lambda x: x.do_qty):
    #             qty_received = qty_received_old
    #         qty_received = qty_received if qty_received else qty_received_old
    #         record.qty_received = qty_received
    #         if (record.product_qty > 0 and record.product_qty == qty_received
    #                 and record.state not in ['purchasing', 'done']):
    #             if record.state == 'closed':
    #                 continue
    #             record.state = 'purchasing'
    #         #  order_line.update({'pr_line_id': record.id})
    #         # order_line = purchase.order_line.filtered(lambda x: x.pr_line_id.id == record.id)
    #         # product = order_line.filtered(lambda x: x.product_id.product_tmpl_id.id == record.product_id.id)
    #         ##  采购订单数量 = 当前已处理数量
    #         # record.qty_received = sum(product.mapped('product_qty'))

    # def batch_create_purchase_order(self):
    #     """
    #     批量创建采购单
    #     1, 根据订单汇总
    #     2, 根据供应商汇总
    #     {
    #         'order_id_supplier_id': [order_line]
    #     }
    #     """
    #     supplier_groups = defaultdict(list)
    #     for rec in self:
    #         if not rec.parent_id:
    #             # raise ValidationError(f'产品: {rec.name} 未选中供应商, 无法下单')
    #             return {
    #                 'type': 'ir.actions.client',
    #                 'tag': 'display_notification',
    #                 'params': {
    #                     'title': 'Error',
    #                     'message': _(f'产品: %s 未选中供应商, 无法下单') % rec.name,
    #                     'sticky': False,  # 设置为 False，表示通知不会一直显示
    #                     'timeout': 5000,  # 设置通知显示的时间（单位：毫秒），此处为 5 秒
    #                 }
    #             }
    #         keys = f'order_{rec.requisition_id.id}_supplier_{rec.parent_id.id}'
    #         supplier_groups[keys].append(rec)
    #     purchase_ids = []
    #     for str_keys, lines in supplier_groups.items():
    #         val = str_keys.split('_')
    #         requisition_id = self.env['purchase.requisition'].browse(int(val[1]))
    #         order_line = []
    #         requirement_date = requisition_id.requisition_date
    #         for pr_line in lines:
    #             if pr_line.state == 'purchase':
    #                 continue
    #             if not pr_line.product_id.product_variant_id:
    #                 # raise ValidationError(f'产品: {pr_line.name} 不存在或已归档, 无法下单')
    #                 return {
    #                     'type': 'ir.actions.client',
    #                     'tag': 'display_notification',
    #                     'params': {
    #                         'title': 'Error',
    #                         'message': _('产品: %s 不存在或已归档, 无法下单') % pr_line.name,
    #                         'sticky': False,  # 设置为 False，表示通知不会一直显示
    #                         'timeout': 5000,  # 设置通知显示的时间（单位：毫秒），此处为 5 秒
    #                     }
    #                 }
    #             if pr_line.do_quantity <= 0:
    #                 # raise ValidationError(f'产品: {pr_line.name} 下单数量为0, 无法下单')
    #                 return {
    #                     'type': 'ir.actions.client',
    #                     'tag': 'display_notification',
    #                     'params': {
    #                         'title': 'Error',
    #                         'message': _('产品: %s 下单数量为0, 无法下单') % pr_line.name,
    #                         'sticky': False,  # 设置为 False，表示通知不会一直显示
    #                         'timeout': 5000,  # 设置通知显示的时间（单位：毫秒），此处为 5 秒
    #                     }
    #                 }
    #             prices = pr_line.product_id.seller_ids.filtered(lambda x: x.partner_id.id == pr_line.parent_id.id)
    #             order_line.append((0, 0, {
    #                 'product_id': pr_line.product_id.product_variant_id.id,
    #                 'product_qty': pr_line.do_quantity,
    #                 'do_qty': pr_line.do_quantity,
    #                 'product_uom': pr_line.product_id.uom_po_id.id,
    #                 'taxes_id': [p.id for p in pr_line.product_id.supplier_taxes_id],
    #                 'pr_line_id': pr_line.id,
    #                 'price_unit': prices[0].price if prices else 1,
    #                 'name': pr_line.product_id.display_name,
    #             }))
    #             requirement_date = pr_line.requirement_date
    #             pr_line.do_quantity = pr_line.product_qty - pr_line.qty_received - pr_line.do_quantity
    #             if pr_line.do_quantity == 0:
    #                 pr_line.state = 'purchasing'
    #             else:
    #                 pr_line.state = 'section_purchasing'
    #         res = self.sudo().env['purchase.order'].sudo().create({
    #             'requisition_id': requisition_id.id,
    #             'partner_id': int(val[3]),
    #             'date_order': requirement_date,
    #             'picking_type_id': self.env.ref('stock.picking_type_in').id,
    #             'order_line': order_line,
    #             'origin': requisition_id.name
    #         })
    #         purchase_ids.append(res.id)
    #         requisition_id.update_state()
    #     return {
    #         'name': _('Purchase Orders'),
    #         'type': 'ir.actions.act_window',
    #         'res_model': 'purchase.order',
    #         'view_mode': 'tree,form',
    #         'target': 'current',
    #         'domain': [('id', 'in', purchase_ids)],
    #     }

    @api.onchange('parent_id')
    def onchange_parent_id(self):
        for record in self:
            if record.parent_id:
                pp = record.product_id.seller_ids.filtered(lambda x: x.partner_id.id == record.parent_id.id)
                record.unit_price = pp[0].price if pp else False

    @api.depends('product_id')
    def get_domain_partner(self):
        for record in self.sudo():
            record.description_chi = record.product_id.description_chi
            domain_parent_ids = record.product_id.seller_ids.mapped('partner_id.id')
            record.domain_parent_ids = domain_parent_ids
            if record.parent_id:
                continue
            if len(domain_parent_ids) == 1:
                record.parent_id = domain_parent_ids[0]
            else:
                record.parent_id = False

    def constraint_do_quantity(self):
        for rec in self:
            if rec.state in ['done', 'closed']:
                continue
            # 获取总的下单数量
            domain = [('pr_line_id', '=', rec.id),
                      ('po_state', '!=', 'cancel'),
                      ('product_id', '=', rec.product_id.id)]
            purchase_line = self.env['sb.pr.requisition.line.po.line'].search(domain)
            do_quantity = sum(purchase_line.mapped('do_quantity'))
            if do_quantity == 0:
                rec.state = 'draft'
            elif rec.product_qty == do_quantity:
                rec.state = 'purchasing'
            elif rec.product_qty > do_quantity:
                rec.state = 'section_purchasing'


class PurchaseRequisition(models.Model):
    _name = 'purchase.requisition'
    _description = 'Purchase Requisition'
    _inherit = ['portal.mixin', 'mail.thread.main.attachment', 'mail.activity.mixin']
    _order = 'id desc'

    name = fields.Char('Purchase Request', default='Draft', copy=False, tracking=True)
    origin = fields.Char('Origin', copy=False, tracking=True)
    origin_sale_line_id = fields.Many2one('sale.order.line', string='Sale Order Line', copy=False, readonly=True)
    origin_sale_id = fields.Many2one('sale.order', string='Sale Order', copy=False, readonly=True)
    origin_product_id = fields.Many2one('product.product', 'origin product', copy=False, tracking=True)
    origin_partner_id = fields.Many2one('res.partner', 'origin partner', copy=False, tracking=True)
    applicant = fields.Many2one('res.users', 'Applicant', default=lambda self: self.env.user)
    requisition_date = fields.Date('Requisition Date', default=lambda self: fields.Date.today(), copy=False)
    pr_line_ids = fields.One2many('purchase.requisition.line', 'requisition_id', 'Products', copy=True)
    product_id = fields.Many2one('product.template', 'Product', related='pr_line_ids.product_id', readonly=True)
    state = fields.Selection([
        ('draft', 'Draft'),
        ('wait_approval', 'Waiting for Approval'),
        ('approved', 'Approved'),
        ('countersignature', 'Countersignature'),
        ('section_purchasing', 'Section Purchasing'),  # 部分采购
        ('purchasing', 'Purchasing'),  # 全部采购
        ('closed', 'Closed'),  # 已关闭
        ('create_mate', 'Create Materials'),
        ('rejected', 'Rejected'),
        ('cancel', 'Cancel'),
        ('done', 'Done')
    ], default='draft', string='Status', tracking=True, copy=False)
    sb_workflow_template_id = fields.Many2one('sb.workflow.template', string="Approval Workflow Template",
                                              domain="[('sb_workflow_template_item_ids', '!=', False),"
                                                     "('workflow_type', 'in', ['purchase_requisition'])]")
    sb_workflow_id = fields.Many2one('sb.workflow', string="Current Approval Workflow", copy=False)
    sb_workflow_state = fields.Selection(related="sb_workflow_id.state",
                                         readonly=True, store=True,
                                         string="Approval Status")
    sb_workflow_item_ids = fields.One2many(related="sb_workflow_id.sb_workflow_item_ids", string="Approval Items")
    sb_workflow_attachments_ids = fields.One2many(related="sb_workflow_id.sb_workflow_attachments_ids",
                                                  string="Approval Attachments")
    sb_workflow_ids = fields.One2many('sb.workflow', 'purchase_requisition_id', string="Approval Workflows")
    purchase_ids = fields.One2many('purchase.order', 'requisition_id', string='Purchase Orders', copy=False)
    s_purchase_ids = fields.Many2many('purchase.order',
                                      relation='requisition_purchase_order_rel',
                                      column1='pr_id',
                                      column2='po_id',
                                      string='Purchase Orders', copy=False)
    requisition_text = fields.Text('Equipment Description')
    purchase_requisition_type = fields.Selection([
        ('material', 'Material purchase'),
        ('equipment', 'Equipment purchase')
    ], default='material', string="Purchase Type")

    file_url = fields.Char(string='Attachments Link')
    device_name = fields.Char(string='Device Name')
    qty = fields.Integer(string='Quantity')
    file = fields.Binary(string='File')
    file_name = fields.Char(string='File Name')
    device_type = fields.Selection([
        ('testing', 'Testing equipment'),
        ('it', 'IT equipment'),
        ('production', 'Production equipment'),
        ('electronic', 'Electronic equipment'),
        ('office', 'Office equipment')
    ])

    countersignature = fields.Many2one('sb.workflow', string="Countersignature", copy=False)
    countersignature_item_ids = fields.One2many(related="countersignature.sb_workflow_item_ids",
                                                string="Countersignature Items")
    countersignature_state = fields.Selection(related="countersignature.state", readonly=True,
                                              string="Countersignature Status")
    product_categ_id = fields.Many2one('product.category', string="Product Category",
                                       domain="[('parent_id', '=', False),('code', '!=', False)]")
    product_tmpl_ids = fields.One2many('product.template', 'requisition_id', 'Product Template')
    close_remark = fields.Text('Close Remarks')
    approval_text = fields.Text('Approval Text', compute='_compute_approve', store=True)
    remark = fields.Text('Remark', copy=False)
    product = fields.Text('Product', compute='_compute_get_product')
    supplier = fields.Text('Supplier', compute='_compute_get_product')
    presets_bom_id = fields.Many2one('sb.presets.bom', string="预设BOM")
    presets_bom_diff_id = fields.Many2one('sb.presets.bom.diff', string="预设BOM差异表")
    default_po = fields.Boolean(string="是否走PO", default=True)

    requisition_type = fields.Selection([
        ('stock', 'Inventory requisition'),
        ('order', 'Order'),
        ('risk', 'Risk'),
    ], string='requisition type', default='stock', required=True)

    def default_po_done(self):
        """
        弹框选择完成的数量
        """
        self.ensure_one()
        return {
            'name': _('Requisition Done'),
            'type': 'ir.actions.act_window',
            'res_model': 'requisition.default.done.wizard',
            'view_mode': 'form',
            'target': 'new',
            'context': {
                'default_requisition_id': self.id,
                'default_line_ids': [(0, 0, {
                    'pr_line_id': i.id,
                    'do_quantity': i.qty_received_virtual if i.qty_received_virtual > 0 else i.product_qty
                }) for i in self.pr_line_ids.filtered(lambda x: x.state in ['draft', 'section_purchasing'])]
            }
        }

    def unlink(self):
        """
        删除时检查状态，允许草稿状态删除
        """
        if self.filtered(lambda x: x.state != 'draft'):
            raise ValidationError(_("Deletion error, only the draft state is allowed to be deleted!"))
        # 仅限系统管理员才可删除
        if not self.env.user.has_group('base.group_system') and self.state != 'draft':
            raise ValidationError(_("Delete error, please use system administrator privileges!"))
        ret = super(PurchaseRequisition, self).unlink()
        return ret

    @api.depends('pr_line_ids')
    def _compute_get_product(self):
        for record in self:
            if record.pr_line_ids:
                record.product = ''.join(record.pr_line_ids[0].mapped('product_id.name'))
                supplier = record.pr_line_ids.parent_id or record.pr_line_ids.domain_parent_ids
                record.supplier = supplier[0].name if supplier else ''
            else:
                record.product = ''
                record.supplier = ''

    # 修复po明细关联到pr明细异常历史数据功能
    # def calc_po_to_pr_related(self):
    #     old_related = []
    #     for l_row in self.pr_line_ids.filtered(lambda x: x.product_qty < x.qty_received):
    #         # l_row.requisition_id.purchase_ids.order_line
    #         l_row.requisition_id.purchase_ids.order_line.mapped(lambda x: (x.product_id.id, x.product_qty))
    #         un_relate_line = l_row.requisition_id.purchase_ids.order_line.filtered(
    #             lambda x: x.product_id.product_tmpl_id.id == l_row.product_id.id \
    #                       and x.product_qty == l_row.do_quantity \
    #                       and x.product_uom == l_row.product_id.uom_po_id
    #         )
    #         check_related = un_relate_line.filtered(lambda x: x.id not in [x[-1] for x in old_related])
    #         if not check_related:
    #             continue
    #         old_related = [(l_row.id, un_relate_line[0].id)]
    #         if not check_related:
    #             continue
    #         check_related[0].pr_line_id = l_row.id

    # def initiate_countersignature(self):
    #     if not self.file_url:
    #         raise ValidationError(_('Please fill in the attachment link first'))
    #     workflow_sum = len(self.sb_workflow_ids) + 1
    #     new_wf_id = self.env['sb.workflow'].sudo().create({
    #         'name': self.name + '- Countersignature' + '-' + str(workflow_sum),
    #         'approval_time': fields.Datetime.now(),
    #         'workflow_type': 'purchase_requisition',
    #         'purchase_requisition_id': self.id,
    #     })
    #     self.countersignature = new_wf_id.id
    #     # self.countersignature = self.env.ref('sb_workflow_purchase.countersignature_workflow').id
    #     # 根据模板生成审批项
    #     item_audit_levels = []
    #     for item in self.env.ref('sb_workflow_purchase.countersignature_workflow').sb_workflow_template_item_ids:
    #         new_item = self.env['sb.workflow.item'].sudo().create({
    #             'name': item.name,
    #             'user_group_ids': item.user_group_ids.ids,
    #             'audit_level': item.audit_level,
    #             'sb_workflow_id': new_wf_id.id,
    #             'state': 'wait_forward'
    #         })
    #         # 收集生成的审核项的所有层级
    #         item_audit_levels.append(new_item.audit_level)
    #         # 去重排序
    #         item_audit_levels = list(set(item_audit_levels))
    #         item_audit_levels.sort()
    #         # 开放最低层级的神匹配
    #         for n_item in new_wf_id.sb_workflow_item_ids:
    #             if n_item.audit_level == item_audit_levels[0]:
    #                 n_item.state = 'approval'

    # def action_view_template(self):
    #     view_tree = self.env.ref('product.product_template_tree_view')
    #     view_form = self.env.ref('product.product_template_only_form_view')
    #     return {
    #         'name': _('Product Template'),
    #         'type': 'ir.actions.act_window',
    #         'view_mode': 'tree,form',
    #         'res_model': 'product.template',
    #         'domain': [('id', 'in', self.product_tmpl_ids.ids)],
    #         'views': [(view_tree.id, 'tree'), (view_form.id, 'form')],
    #         'target': 'current',
    #     }

    def write(self, vals):
        res = super(PurchaseRequisition, self).write(vals)
        # self.check_product_id()
        if vals.get('file_url'):
            message = vals['file_url']
            if 'www' not in vals['file_url']:
                # 如果不包含 "www"，则在前面添加 "http://www."
                message = 'http://www.' + vals['file_url']
            mark = Markup('<p><a href="%s" target="_blank">%s</a></p>') % (message, 'File Link')
            self.sudo().message_post(
                body=mark,
                message_type='comment',
            )
        return res

    @api.model
    def create(self, vals_list):
        res = super(PurchaseRequisition, self).create(vals_list)
        res.check_product_id()
        return res

    @api.constrains('pr_line_ids')
    def check_product_id(self):
        product_ids = []
        for record in self.pr_line_ids:
            if record.product_id in product_ids:
                raise ValidationError(_('Duplicate product %s') % record.product_id.name)
            product_ids.append(record.product_id)

    def copy(self, default=None):
        res = super().copy(default)
        res.applicant = self.env.user
        return res

    # @api.depends('sb_workflow_state', 'purchase_ids', 'purchase_ids.state', 'countersignature_state',
    #              'pr_line_ids.state')
    # def _compute_state(self):
    #     for item in self.with_user(SUPERUSER_ID):
    #         if item.state == 'closed':
    #             continue
    #         if item.sb_workflow_state == 'approve':
    #             if item.countersignature_state == 'approval':
    #                 item.state = 'countersignature'
    #             elif item.countersignature_state == 'fail_to_approve':
    #                 item.state = 'rejected'
    #             else:
    #                 item.state = 'approved'
    #             if item.purchase_ids.filtered(lambda x: x.state != 'cancel'):
    #                 # 更新状态, 部分采购, 完全采购
    #                 line_state = item.pr_line_ids.mapped('state')
    #                 state = 'purchasing'
    #                 for rec in line_state:
    #                     if rec == 'draft' or rec == 'section_purchasing':
    #                         state = 'section_purchasing'
    #                         break
    #                 # if item.pr_line_ids.filtered(lambda x: x.state == 'closed'):
    #                 #     item.state = 'closed'
    #                 #     return
    #                 item.state = state
    #             else:
    #                 item.state = 'countersignature' if item.purchase_requisition_type == 'equipment' else 'approved'
    #         elif item.sb_workflow_state == 'approval':
    #             item.state = 'wait_approval'
    #         elif item.sb_workflow_state == 'fail_to_approve':
    #             item.state = 'rejected'
    #         else:
    #             item.state = 'draft'

    @api.depends('sb_workflow_item_ids', 'sb_workflow_item_ids.approval_instructions')
    def _compute_approve(self):
        for record in self:
            if record.sb_workflow_item_ids:
                text = ''
                for txt in record.sb_workflow_item_ids:
                    if not txt.approval_instructions:
                        continue
                    text += f"""{txt.approval_instructions}
                    """
                record.approval_text = text
            else:
                record.approval_text = False

    @api.constrains('sb_workflow_state')
    def _constrains_sb_workflow_state(self):
        for record in self.sudo():
            if record.state in ['draft', 'closed']:
                continue
            if record.sb_workflow_state == 'approve':
                record.state = 'approved'

                # 审批通过，在途锁定生效
                self.env['s.product.lock'].sudo().search([
                    ('purchase_requisition_id', '=', record.id),
                    ('active', 'in', (False, True)),
                    ('cancel', '=', False)
                ]).action_unarchive()
            elif record.sb_workflow_state == 'fail_to_approve':
                record.state = 'rejected'

    def update_state(self):
        pur_req_ids = self.sudo()
        for record in pur_req_ids:
            pr_line_ids = record.pr_line_ids
            if not pr_line_ids:
                record.state = 'draft'
                return
            closed_ids = pr_line_ids.filtered(lambda x: x.state == 'closed')
            draft_ids = pr_line_ids.filtered(lambda x: x.state == 'draft')
            purchasing_ids = pr_line_ids.filtered(lambda x: x.state in ['purchasing', 'done'])
            if len(pr_line_ids) == len(closed_ids):
                record.state = 'closed'
            elif len(pr_line_ids) == len(closed_ids + purchasing_ids):  # 关闭+完成+全部请购
                record.state = 'purchasing'
            elif len(pr_line_ids) == len(closed_ids + draft_ids):  # 未开始
                record.state = 'approved'
            else:
                record.state = 'section_purchasing'

    def close_requisition(self):
        """关闭请购
        系统管理员操作
        """
        self.ensure_one()
        # if not self.user_has_groups('base.group_erp_manager'):
        #     raise UserError('Insufficient authority!')
        view_id = self.env.ref('sb_workflow_purchase.view_requisition_close_reason_wizard_form')
        return {
            'type': 'ir.actions.act_window',
            'name': _('Close'),
            'res_model': 'purchase.close.reason.wizard',
            'view_id': view_id.id,
            'view_type': 'form',
            'view_mode': 'form',
            'target': 'new',
            'context': {'default_pur_req_id': self.id},
        }

    def open_create_purchase_wizard(self):
        self = self.sudo().filtered(lambda x: x.state in ['approved', 'section_purchasing'])
        prm = self.env['purchase.requisition.main.wizard'].create({
            'purchase_requisition_ids': self.ids,
        })
        # 关闭, 全部采购, 完成 不会提供可选创建
        for pr_line in self.pr_line_ids.filtered(lambda x: x.state not in ['closed', 'purchasing', 'done']):
            if pr_line.product_qty - pr_line.qty_received <= 0:
                continue
            domain_parent_ids = pr_line.product_id.seller_ids.mapped('partner_id.id')
            self.env['purchase.requisition.create.wizard'].create({
                'prm_id': prm.id,
                'product_id': pr_line.product_id.id,
                'domain_parent_ids': domain_parent_ids,
                'pr_line_id': pr_line.id,
                'parent_id': domain_parent_ids[0] if domain_parent_ids else False,
                'unit_price': pr_line.product_id.seller_ids[0].price if domain_parent_ids else 1,
                'do_quantity': pr_line.product_qty - pr_line.qty_received,
            })
        return {
            'name': _('Create Purchase Order'),
            'type': 'ir.actions.act_window',
            'view_type': 'form',
            'view_mode': 'form',
            'res_model': 'purchase.requisition.main.wizard',
            'target': 'new',
            'res_id': prm.id,
            'context': {
                'default_purchase_requisition_ids': self.ids,
            },
        }

    def _determine_type_and_detect(self):
        for rec in self:
            if rec.purchase_requisition_type == 'material':
                if not rec.pr_line_ids:
                    raise UserError(_("Please add purchased items"))
                for line in rec.pr_line_ids:
                    if line.product_qty <= 0:
                        raise ValidationError(_('Quantity must be greater than 0.'))
            if rec.purchase_requisition_type == 'equipment':
                if not rec.requisition_text:
                    raise UserError(_("Please add equipment purchase"))

    def create_workflow(self):
        self.ensure_one()
        self._determine_type_and_detect()
        if self.sb_workflow_template_id:
            if len(self.sb_workflow_ids) > 0:
                workflow_sum = len(self.sb_workflow_ids) + 1
            else:
                workflow_sum = 1
            self.state = 'wait_approval'
            if self.name == 'Draft' or not self.name:
                self.name = self.env['ir.sequence'].next_by_code('purchase.requisition')
                self.message_post(
                    body=_('Submit for approval'),
                )
            # 创建审批流
            new_wf_id = self.env['sb.workflow'].sudo().create({
                'name': self.name + '- Approval Flow' + '-' + str(workflow_sum),
                'approval_time': fields.Datetime.now(),
                'workflow_type': 'purchase_requisition',
                'purchase_requisition_id': self.id,
                'remark': self.remark or ''
            })
            self.sb_workflow_id = new_wf_id.id
            # self.requisition_date = fields.Date.today()
            # 根据模板生成审批项
            item_audit_levels = []
            for item in self.sb_workflow_template_id.sb_workflow_template_item_ids:
                new_item = self.env['sb.workflow.item'].sudo().create({
                    'name': item.name,
                    'user_group_ids': item.user_group_ids.ids,
                    'audit_level': item.audit_level,
                    'sb_workflow_id': new_wf_id.id,
                    'state': 'wait_forward'
                })
                # 收集生成的审核项的所有层级
                item_audit_levels.append(new_item.audit_level)
            # 去重排序
            item_audit_levels = list(set(item_audit_levels))
            item_audit_levels.sort()
            # 开放最低层级的神匹配
            for n_item in new_wf_id.sb_workflow_item_ids:
                if n_item.audit_level == item_audit_levels[0]:
                    n_item.state = 'approval'
        else:
            raise ValidationError(_('Please select an approval template.'))

    # @api.depends('sb_workflow_ids')
    # def _compute_sb_workflow_ids_sum(self):
    #     for item in self:
    #         if item.sb_workflow_ids:
    #             item.sb_workflow_ids_sum = len(item.sb_workflow_ids)
    #         else:
    #             item.sb_workflow_ids_sum = 0

    def action_view_workflow(self):
        return {
            'name': _('Approval Flow'),
            'type': 'ir.actions.act_window',
            'res_model': 'sb.workflow',
            'view_mode': 'tree,form',
            'target': 'current',
            'domain': [('purchase_requisition_id', '=', self.id)],
        }

    def action_view_purchase(self):
        return {
            'name': _('Purchase Orders'),
            'type': 'ir.actions.act_window',
            'res_model': 'purchase.order',
            'view_mode': 'tree,form',
            'target': 'current',
            'domain': [('id', 'in', self.s_purchase_ids.ids)],
        }

    def action_workflow(self):
        return {
            'name': _('Approval Flow'),
            'type': 'ir.actions.act_window',
            'res_model': 'sb.workflow',
            'view_mode': 'form',
            'res_id': self.sb_workflow_id.id,
            # 'views': [(self.env.ref('').id, 'form')],
            'target': 'current',
        }

    # def confirm_file(self):
    #     """确认文件
    #     执行后订单状态须改为完成
    #     """
    #     self.state = 'create_mate'

    # def action_create_materiel(self):
    #     """
    #     创建物料向导
    #     """
    #     view = self.env.ref('product.product_template_only_form_view')
    #     return {
    #         'name': _('Product Template'),
    #         'type': 'ir.actions.act_window',
    #         'view_type': 'form',
    #         'view_mode': 'form',
    #         'res_model': 'product.template',
    #         'views': [(view.id, 'form')],
    #         'target': 'current',
    #         'context': {
    #             'default_name': self.device_name,
    #             'default_detailed_type': 'product',
    #             'default_requisition_id': self.id,
    #         },
    #     }

    def button_cancel(self):
        if self.sudo().purchase_ids.filtered(lambda x: x.state != 'cancel'):
            # raise ValidationError('存在采购单, 无法取消')
            return {
                'type': 'ir.actions.client',
                'tag': 'display_notification',
                'params': {
                    'title': 'Error',
                    'message': _('存在进行中的采购单, 无法取消, 可先取消采购单后操作'),
                    'sticky': False,  # 设置为 False，表示通知不会一直显示
                    'timeout': 5000,  # 设置通知显示的时间（单位：毫秒），此处为 5 秒
                }
            }
        self.state = 'cancel'
        self.sb_workflow_id = False

    def button_set_to_done(self):
        self.ensure_one()
        # if self.filtered(lambda x:x.state != 'section_purchasing'):
        #     raise ValidationError('不在部分采购状态，不能手动完成')
        self.update_state()
        if self.state in ['purchasing', 'closed']:
            return
        if not self.pr_line_ids.filtered(lambda x: x.product_qty != x.qty_received):
            raise ValidationError('没有发现未完成的数量，不能手动完成')
        view_id = self.env.ref('sb_workflow_purchase.view_purchase_close_reason_wizard_form')
        return {
            'type': 'ir.actions.act_window',
            'name': '输入关闭原因',
            'res_model': 'purchase.close.reason.wizard',
            'view_id': view_id.id,
            'view_type': 'form',
            'view_mode': 'form',
            'target': 'new',
            'context': {'default_pur_req_id': self.id, 'default_to_state': 'closed'},
        }

    # def button_set_to_draft(self):
    #     if self.sudo().purchase_ids:
    #         raise ValidationError('存在采购单, 无法取消')
    #     self.sb_workflow_id.unlink()
    #     self.state = 'draft'


class PurchaseRequisitionLineTrace(models.Model):
    _name = 'purchase.requisition.line.trace'
    _description = 'Purchase Requisition Line Trace'
    _auto = False

    requisition_date = fields.Date('Requisition Date')
    location_id = fields.Many2one('stock.location', string='Location Name')
    product_id = fields.Many2one('product.template', 'Material specification')
    product_qty = fields.Float('Quantity', digits='Product Unit of Measure', help='Product Unit of Measure')
    qty_received = fields.Float("Received Qty", digits='Product Unit of Measure')
    less_material = fields.Float("Less Material", digits='Product Unit of Measure')
    requirement_date = fields.Date('Requirement Date')
    s_date_planned = fields.Date(string='Expected Arrival', compute='compute_move_line')
    date_done = fields.Datetime('Date of Transfer', compute='compute_move_line')
    purchase_ids = fields.Many2many('purchase.order', 'Purchase Order Number', compute='compute_move_line')
    purchase_requisition_id = fields.Many2one('purchase.requisition', string='Purchase Requisition')
    requisition_line_id = fields.Many2one('purchase.requisition.line', string='Purchase Requisition')
    state = fields.Selection([
        ('tobe_done', 'To Be Done'),
        ('done', 'Done')
    ], string='Done State')

    @api.depends('product_id')
    def compute_move_line(self):
        for item in self:
            pplo = self.env['sb.pr.requisition.line.po.line'].sudo().search(
                [('pr_line_id', '=', item.requisition_line_id.id)])
            purchase_line = pplo.po_line_id
            # parent_id = item.product_id.categ_id.parent_id or item.product_id.categ_id
            # rule = self.env['stock.putaway.rule'].sudo().search([('category_id', '=', parent_id.id)],limit=1)
            picking_id = purchase_line.move_ids.picking_id.filtered(lambda x: x.state == 'done')
            purchase_ids = purchase_line.order_id.filtered(lambda x: x.state != 'cancel')
            # item.location_id = rule.location_out_id.id
            item.date_done = picking_id[0].date_done if picking_id and item.qty_received > 0 else False
            item.purchase_ids = [(6, 0, purchase_ids.ids)]
            item.s_date_planned = purchase_ids[0].s_date_planned if purchase_ids else False

    @property
    def _table_query(self):
        query = f""" 
            WITH RECURSIVE
                new_pc AS (SELECT ID, parent_id, ARRAY [ ID ] pa
                           FROM product_category
                           WHERE parent_id IS NULL
                           UNION ALL
                           SELECT pc.ID,
                                  pc.parent_id,
                                  npc.pa || ARRAY [ pc.ID ] AS pa
                           FROM product_category pc,
                                new_pc npc
                           WHERE pc.parent_id = npc.ID),
                location1 AS (SELECT id FROM stock_location WHERE barcode = 'WH-STOCK' AND usage = 'internal'),
                location2 AS (SELECT id FROM stock_location WHERE location_id = (SELECT id FROM location1)),
                location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2),
                location_cte AS (SELECT ID,
                                        location_id
                                 FROM stock_location
                                 WHERE barcode = 'WH-PREPRODUCTION'
                                 UNION ALL
                                 SELECT e.ID,
                                        e.location_id
                                 FROM stock_location e,
                                      location_cte r
                                 WHERE e.location_id = r.ID),
            
                purchase_ol as (select distinct spre.pr_line_id   AS prl_id,
                                                pol.id   as po_line_id,
                                                prl.product_qty,
                                                spre.do_quantity,
                                                pr.id    as requisition_id,
                                                prl.requirement_date as requirement_date
                                FROM purchase_order_line pol
                                         left join sb_pr_requisition_line_po_line spre on spre.po_line_id = pol.id
                                         left join purchase_requisition_line prl on prl.id = spre.pr_line_id
                                         left join purchase_requisition pr on pr.id = prl.requisition_id
                                where spre.pr_line_id is not null
                                  and pol.state != 'cancel'),
                purchase_ol_group_by as (select prl_id,
                                                po_line_id,
                                                sum(do_quantity) as product_qty,
                                                requisition_id,
                                                requirement_date
                                         from purchase_ol
                                         group by prl_id, po_line_id, requisition_id, requirement_date),
            
                requisition_ol as (select prl.id          as prl_id,
                                          -1              as po_line_id,
                                          prl.product_qty as product_qty,
                                          pr.id           as requisition_id,
                                          prl.requirement_date        as requirement_date
                                   from purchase_requisition_line prl
                                            left join purchase_requisition pr on pr.id = prl.requisition_id
                                   where pr.state in ('wait_approval', 'approved')),
            
                purchase_main as (select *
                                  from purchase_ol_group_by
                                  union all
                                  select *
                                  from requisition_ol),
                purchase_move as (
                    -- 统计完成入库数量
                    SELECT pol.id                 as po_line_id,
                           sum(sm.s_purchase_qty) as qty_received
                    FROM purchase_order_line pol
                             LEFT JOIN stock_move sm ON pol.ID = sm.purchase_line_id
                    where sm.state = 'done'
                      and sm.location_dest_id in (select id from location3)
                    group by pol.id)
            
            select pm.prl_id            as id,
                   pm.product_qty       as product_qty,
                   pm2.qty_received     as qty_received,
                   case
                       when COALESCE(pm.product_qty, 0) - COALESCE(pm2.qty_received, 0) <= 0 then
                           0
                       else COALESCE(pm.product_qty, 0) - COALESCE(pm2.qty_received, 0)
                       END              AS less_material,
                   CASE
                       WHEN COALESCE(pm.product_qty, 0) - COALESCE(pm2.qty_received, 0) > 0 and prl.state != 'done' THEN
                           'tobe_done'
                       ELSE 'done'
                       END              AS STATE,
                   prl.product_id       as product_id,
                   pm.requirement_date as requirement_date,  -- 明细的需求时间
                   pm.prl_id            as requisition_line_id,
                   spr.location_out_id  as location_id,
                   pm.requisition_id    as purchase_requisition_id,
                   pr.requisition_date  as requisition_date
            from purchase_main pm
                     left join purchase_move pm2 on pm2.po_line_id = pm.po_line_id
                     LEFT JOIN purchase_requisition_line prl on prl.id = pm.prl_id
                     left join purchase_requisition pr on pr.id = pm.requisition_id
                     LEFT JOIN product_template pt on pt.id = prl.product_id
                     LEFT JOIN new_pc np on np.id = pt.categ_id
                     LEFT JOIN stock_putaway_rule spr ON np.pa[1] = spr.category_id
            where NOT EXISTS (SELECT 1 FROM location_cte lc WHERE lc.id = spr.location_out_id)
        """
        return query
